package com.monlyu.util;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URL;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WritableFont.FontName;

/********************************************************************************
 * 
 * <b>功能描述☞</b>借助JExcelApi实现对Excel的操作<br>
 * 
 * <b>开发公司☞</b>重庆唯尼科技有限公司<br>
 * <b>版权☞</b>本文件归重庆唯尼科技有限公司所有<br>
 * <b>项目名☞</b>NewsSys<br>
 * <b>文件路径☞</b>com.monlyu.util.ExcelUtil.java<br>
 * <b>开发日期☞</b>2010-3-10 <br>
 * 
 * @author monlyu
 * @since JDK 5
 * @version 1.0.1
 * @see jxl.jar
 * 
 ********************************************************************************/
public class ExcelUtil {
	/**
	 * 创建工作簿
	 * 
	 * @param file
	 *            需要保存的文件
	 * @return WritableWorkbook 工作目录
	 * @throws IOException
	 *             抛出IO异常
	 */
	public static WritableWorkbook ininWorkbook(File file) throws IOException {
		return Workbook.createWorkbook(file);
	}

	/**
	 * 创建一个工作流的work
	 */
	public static WritableWorkbook ininWorkbook(OutputStream os) throws IOException {
		return Workbook.createWorkbook(os);
	}

	/**
	 * 创建工作簿
	 * 
	 * @param file
	 *            需要保存的文件
	 * @param settings
	 *            设置
	 * @return WritableWorkbook 工作目录
	 * @throws IOException
	 *             抛出IO异常
	 */

	public static WritableWorkbook ininWorkbook(File file, WorkbookSettings settings) throws IOException {
		return Workbook.createWorkbook(file, settings);
	}

	/**
	 * 添加一个工作的sheet 程序会自动的添加上这个是第几个的sheet
	 * 
	 * @param workbook
	 *            工作播
	 * @param sheetName
	 *            工作sheet的名字
	 * @return WritableSheet
	 */
	public static WritableSheet createSheet(WritableWorkbook workbook, String sheetName) {
		return workbook.createSheet(sheetName, workbook.getNumberOfSheets());
	}

	/**
	 * 字符串形式的Cell
	 * 
	 * @param x
	 *            行
	 * @param y
	 *            列
	 * @param value
	 *            值
	 * 
	 */
	public static Label cell(int x, int y, String value) {
		return new Label(x, y, value);
	}

	/**
	 * @param x
	 *            行
	 * @param y
	 *            列
	 * @param fontName
	 *            字体名
	 * @param size
	 *            字体大小
	 * @param args
	 *            第一个参数为是否加粗<br>
	 *            第二个参数为是否需要斜体 参考<br>
	 *            <b>本方法提供部分参数,具体参数如下</b><br>
	 *            <blockquote><code>WritableFont blue = new WritableFont(<br>
	 *   			WritableFont.ARIAL,//字体<br>
	 *   			WritableFont.DEFAULT_POINT_SIZE,//字体大小<br>
	 *   			WritableFont.NO_BOLD,//为粗体还是不需要粗体<br>
	 *   			false,//是否为斜体<br>
	 *   			UnderlineStyle.NO_UNDERLINE,//下划线的样式<br>
	 *   			Colour.BLUE);//字体颜色<br>
	 *  WritableCellFormat blueFormat = new WritableCellFormat(blue);<br>
	 * 	lr = new Label(2, 18, "Blue", blueFormat);<br></code> </blockquote>
	 * 
	 * @see ExcelUtil#cell 发生异常.返回cell
	 */
	public static Label cell(int x, int y, String value, FontName fontName, int size, boolean... args) {
		try {
			WritableFont font = null;
			if (args != null) {
				if (args.length > 1) {
					font = new WritableFont(fontName, size, args[0] ? WritableFont.BOLD : WritableFont.NO_BOLD, args[1]);
				} else {
					font = new WritableFont(fontName, size, args[0] ? WritableFont.BOLD : WritableFont.NO_BOLD);
				}
			} else {
				font = new WritableFont(fontName, size);
			}
			WritableCellFormat format = new WritableCellFormat(font);
			format.setWrap(true);
			return new Label(x, y, value, format);
		} catch (Exception e) {
			return cell(x, y, value);
		}
	}

	/** 添加超链接 */

	public static WritableHyperlink cell(int col, int row, int lastcol, int lastrow, String url) {
		try {
			final URL netUrl = new URL(url);
			WritableHyperlink wh = new WritableHyperlink(0, 30, 8, 31, netUrl);
			return wh;
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 设置Row的高度
	 */
	public static void setRowHeight(WritableSheet sheet, int row, int height) {
		try {
			sheet.setRowView(row, height);
		} catch (Exception e) {
			// 不处理异常
		}
	}

	/**
	 * 设置行的宽度
	 */
	public static void setColWidth(WritableSheet sheet, int col, int width) {
		try {
			sheet.setColumnView(col, width);
		} catch (Exception e) {
			// 不处理异常
		}
	}

	/**
	 * 添加字符型的行
	 * 
	 * @param sheet
	 *            写入sheet
	 * @param rowNo
	 *            列号
	 * @param colBegin
	 *            开始的行号
	 * @param cells
	 *            列名
	 */
	public static void addRow(WritableSheet sheet, int rowNo, int colBegin, String... cells) {
		for (int i = 0; i < cells.length; i++) {
			try {
				sheet.addCell(cell(colBegin + i, rowNo, cells[i]));
			} catch (Exception e) {
				LogUtil.log(ExcelUtil.class).debug("添加列名失败");
				continue;
			}
		}
	}

	/**
	 * 添加一行
	 * 
	 */
	public static void addRow(WritableSheet sheet, Label... labels) {
		for (Label label : labels) {
			try {
				sheet.addCell(label);
			} catch (Exception e) {
				continue;
			}
		}
	}

	/**
	 * 保存excel
	 */
	public static void save(WritableWorkbook workbook) {
		try {
			workbook.write();
			workbook.close();
		} catch (Exception e) {
			LogUtil.logWithTime(ExcelUtil.class, "保存Excel失败", e);
		}
	}
}
